﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using SqlSugar;
using 数据迁移工具.Factory;

namespace 数据迁移工具.Common.DbContext
{
    public class DmDbContext : SugarDbContext
    {
        private CancellationToken ct = new CancellationToken();
        private static Dictionary<string, MatchEvaluator> DmToMySqlFuncMapping = new Dictionary<string, MatchEvaluator>();

        static DmDbContext()
        {
            // RegexOptions.Singleline：通过添加 RegexOptions.Singleline 选项，.* 将能够匹配换行符，即跨越多行进行匹配。
            // 匹配  NVL 结构 => IFNULL(exp1,exp2) true => exp1  false => exp2
            string pattern1 = @"NVL\s*\((?<column>.*?)\s*,\s*(?<val>.*?)\s*\)";

            DmToMySqlFuncMapping.Add(pattern1, match =>
            {
                string fieldName = match.Groups["column"].Value.Trim();
                string defaultValue = match.Groups["val"].Value.Trim();
                return $"IFNULL({fieldName}, {defaultValue})";
            });

            string pattern2 = @"LIMIT\s*\((?<val>.*?)\)";
            DmToMySqlFuncMapping.Add(pattern2, match =>
            {
                string val = match.Groups["val"].Value.Trim();
                return $"LIMIT {val}";
            });

            string pattern3_1 = @"CAST\s*\(\s*(?<value>.*?)\s*AS\s*INT\s*\)";
            string pattern3_2 = @"CAST\s*\(\s*(?<value>.*?)\s*AS\s*BIGINT\s*\)";
            MatchEvaluator evaluator = match =>
            {
                string value = match.Groups["value"].Value.Trim();
                return $"CAST({value} AS SIGNED)";
            };
            DmToMySqlFuncMapping.Add(pattern3_1, evaluator);
            DmToMySqlFuncMapping.Add(pattern3_2, evaluator);
        }

        public DmDbContext(ConnectionConfig config) : base(config)
        {
        }

        public override List<TableInfo> GetTableInfos(string dbName)
        {
            string sql = $"SELECT OWNER  as DbName,OBJECT_NAME as TbName,OBJECT_TYPE as TbType FROM DBA_OBJECTS WHERE OWNER = '{dbName}' and OBJECT_TYPE in ('TABLE')";
            return DB.Ado.SqlQuery<TableInfo>(sql);
        }

        protected override List<ViewDefinition> GetViewDefinitions(string mode)
        {
            string sql = $"SELECT OWNER as TABLE_NAME,VIEW_NAME,TEXT as VIEW_DEFINITION FROM ALL_VIEWS where OWNER = '{mode}';";
            return DB.Ado.SqlQuery<ViewDefinition>(sql);
        }

        public override void ViewConvert(string srcMode,DbType dbType, string targetId, string dstDbName)
        {
            List<ViewDefinition> definitions = GetViewDefinitions(srcMode);
            switch (dbType)
            {
                case DbType.MySql:
                    DmToMySqlView(definitions,targetId,dstDbName);
                    break;
                case DbType.SqlServer:
                    break;
                case DbType.Sqlite:
                    break;
                case DbType.Oracle:
                    break;
                case DbType.PostgreSQL:
                    break;
                case DbType.Dm:
                    break;
                default:
                    break;
            }
        }

        /// <summary>
        /// Dm 视图转 MySql 视图
        /// </summary>
        /// <param name="definitions"></param>
        /// <param name="dstId"></param>
        /// <param name="dstDbName"></param>
        /// <exception cref="Exception"></exception>
        public void DmToMySqlView(List<ViewDefinition> definitions, string dstId, string dstDbName)
        {
            List<ViewDefinition> errorViews = new List<ViewDefinition>();

            ConnectionConfig? connConfig = ConnectionConfigs.FirstOrDefault(c => c.ConfigId == dstId);
            if (connConfig == null)
                throw new Exception("指定的数据源Id未找到");
            SugarDbContext dstContext = DbFactory.CreateDbContext(DbType.MySql,connConfig);
            string viewTemplate = "CREATE OR REPLACE VIEW `{0}`.`{1}` AS {2}";
            // 执行两次的目的，防止存在视图之间相互依赖时，第一次执行时因依赖视图未被创建而导致的视图创建失败问题
            int count = 1;
            RegexOptions options = RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase;
            while (count <= 2)
            {
                foreach (var definition in definitions)
                {
                    if (count == 1)
                    {
                        string createViewStr = string.Format(viewTemplate, dstDbName, definition.VIEW_NAME, definition.VIEW_DEFINITION);
                        definition.VIEW_DEFINITION = createViewStr.Replace("“", "").Replace("\"", "`");
                        if (DmToMySqlFuncMapping.Count > 0)
                        {
                            foreach (var item in DmToMySqlFuncMapping)
                            {
                                definition.VIEW_DEFINITION = Regex.Replace(definition.VIEW_DEFINITION, item.Key, item.Value, options, TimeSpan.FromSeconds(2));
                            }
                        }
                    }

                    bool res = CreateView(dstContext, definition.VIEW_NAME, definition.VIEW_DEFINITION);
                    // 如果第一次执行没有任何失败就不再执行第二次
                    if (!res)
                    {
                        errorViews.Add(definition);
                    }
                    else
                    {
                        ConsoleLog.Success("RichText", $"视图 {definition.VIEW_NAME} 创建成功.");
                    }
                }
                if (errorViews.Count == 0) break;
                definitions.Clear();
                definitions.AddRange(errorViews);
                errorViews.Clear();
                count++;
            }

            foreach (var errorView in definitions)
            {
                ConsoleLog.Error("RichText", $"视图 {errorView.VIEW_NAME} 创建失败: {errorView.VIEW_DEFINITION}\n");
                string path = Directory.GetCurrentDirectory() + "\\ViewLog";
                if (!Directory.Exists(path))
                    Directory.CreateDirectory(path);
                File.WriteAllText($"{path}\\{errorView.VIEW_NAME}.log", errorView.VIEW_DEFINITION);
            }
        }

        public override void DbMigrate(DbType targetType, string targetId, string dbName, string namesp = "Default.Models")
        {
            switch (targetType)
            {
                case DbType.MySql:
                    DmToMySqlMigrate(ConfigId,targetId, dbName, namesp);
                    break;
                case DbType.SqlServer:
                    break;
                case DbType.Sqlite:
                    break;
                case DbType.Oracle:
                    break;
                case DbType.PostgreSQL:
                    break;
                case DbType.Dm:
                    break;
                default:
                    break;
            }
        }

        /// <summary>
        /// 达梦数据向MySQL迁移
        /// </summary>
        /// <param name="sourceId">达梦数据源Id</param>
        /// <param name="targetId">MySQL数据源Id</param>
        /// <param name="dbName">数据库名称</param>
        private void DmToMySqlMigrate(string sourceId, string targetId, string srcDbName, string namesp)
        {
            if (DynamicHelper.ModelAssembly == null)
            {
                ConsoleLog.Error("RichText", "实体类程序集未初始化");
                return;
            }
            if (ConnectionConfigs == null || ConnectionConfigs.Count == 0)
                throw new Exception("请检查是否初始化 SugarDbContext.ConnectionConfigs");

            ConnectionConfig? connConfig = ConnectionConfigs.FirstOrDefault(c => c.ConfigId == sourceId);
            if (connConfig == null)
                throw new Exception("指定的数据源Id未找到");
            SqlSugarClient client = new SqlSugarClient(connConfig);

            string sql1 = $"select TABLE_NAME from ALL_ALL_TABLES where OWNER = '{srcDbName}'";
            List<string> tables = client.Ado.SqlQuery<string>(sql1);
            // tables = sugarDbContext.DB.Queryable("ALL_ALL_TABLES", "tb").Where("tb.OWNER = 'CIMS2023'").Select<string>("TABLE_NAME").ToList();
            tableRowMap.Clear();
            foreach (var table in tables)
            {
                string sql = $"select count(*) from {table}";
                int count = client.Ado.GetInt(sql);
                tableRowMap.Add(table, count);
                ConsoleLog.Info("RichText", $"table name: {table},count: {count}");
            }

            Type sugarType = typeof(SqlSugarClient);
            List<Action> actions = new List<Action>();

            foreach (string table in tableRowMap.Keys)
            {
                string typeName = $"{namesp}.{table}";
                Debug.WriteLine(typeName);
                // 动态获取实体类的类型
                Type entityType = DynamicHelper.ModelAssembly.GetType(typeName);
                if (entityType == null)
                {
                    ConsoleLog.Error("RichText", $"未找到类型: {table}");
                    continue;
                }

                actions.Add(() =>
                {
                    string tmpTable = table;
                    int totalRow = QueryAndInsert(sourceId, targetId, sugarType, tmpTable, entityType);
                    ConsoleLog.Success("RichText", $"共插入表: {table}, {totalRow} 行数据");
                });

                //{
                //    // 大数据量插入
                //    Stopwatch stopwatch = Stopwatch.StartNew();
                //    // 获取Fastest的泛型方法
                //    var fastestMethod = sugarType.GetMethod("Fastest").MakeGenericMethod(entityType);
                //    var fastestInstance = fastestMethod.Invoke(targetContext.DB, null);

                //    // 获取PageSize方法
                //    var pageSizeMethod = fastestInstance.GetType().GetMethod("PageSize", new[] { typeof(int) });
                //    pageSizeMethod.Invoke(fastestInstance, new object[] { 10000 });

                //    // 获取BulkCopy方法
                //    Type listType = typeof(List<>).MakeGenericType(entityType);
                //    var bulkCopyMethod = fastestInstance.GetType().GetMethod("BulkCopy", new[] { listType });

                //    var result = bulkCopyMethod.Invoke(fastestInstance, new object[] { list });

                //    stopwatch.Stop();
                //    Console.WriteLine($"执行时间: {stopwatch.ElapsedMilliseconds}");
                //}
            }
            Parallel.Invoke(new ParallelOptions()
            {
                CancellationToken = ct,
                MaxDegreeOfParallelism = 4
            }, actions.ToArray());
        }
    }
}
